﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DatabaseInterface;
using System.Data.SqlClient;
using ObligatorioBD.MerObjects;

namespace ObligatorioBD.Logic
{
    class MerPersistor
    {
        SqlConnection cnx;
        int merId;
        public MerPersistor()
        {
            this.cnx = new Connection().getConnection();
        }
        public void PersistMer(MerObjects.Mer mer)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = this.cnx;
            cmd.Parameters.Add("@Name", System.Data.SqlDbType.VarChar);
            cmd.Parameters.Add("@Owner", System.Data.SqlDbType.VarChar);
            cmd.Parameters["@Name"].Value = mer.Name;
            cmd.Parameters["@Owner"].Value = mer.Owner;
            cmd.CommandText = "INSERT INTO Mer Values(@Name, @Owner, GetDate())";

            this.cnx.Open();
            cmd.ExecuteNonQuery();
            this.cnx.Close();

            cmd.CommandText = "SELECT TOP 1 MerId FROM Mer order by MerId desc";

            this.cnx.Open();
            this.merId = Convert.ToInt32(cmd.ExecuteScalar());
            this.cnx.Close();
            
            PersistEntities(mer);
            PersistRelations(mer);
        }
        public void DeleteMer(Mer mer)
        {
            try
            {
                foreach (Relationship r in mer.GetRelations())
                {
                    DeleteRelation(r);
                }
                foreach (Entity e in mer.GetEntities())
                {
                    DeleteEntity(e);
                }
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = this.cnx;
                cmd.Parameters.Add("@Id", System.Data.SqlDbType.Int);
                cmd.CommandText = "DELETE FROM Mer WHERE MerId = @Id";
                cmd.Parameters["@Id"].Value = mer.ObjectId;
                this.cnx.Open();
                cmd.ExecuteNonQuery();
                this.cnx.Close();
            }
            catch (Exception ex)
            {
                if (this.cnx.State != System.Data.ConnectionState.Closed)
                {
                    this.cnx.Close();
                }
                throw new Exception(ex.Message + "/r Error al borrar Mer " + mer.ObjectId);
            }
           
        }
        public void DeleteRelation(Relationship r)
        {
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = this.cnx;
                cmd.Parameters.Add("@Id", System.Data.SqlDbType.Int);

                cmd.CommandText = "DELETE FROM RelationEntity WHERE RelationId = @Id";
                cmd.Parameters["@Id"].Value = r.ObjectId;
                this.cnx.Open();
                cmd.ExecuteNonQuery();
                this.cnx.Close();

                cmd.CommandText = "DELETE FROM RelationAtributes WHERE RelationId = @Id";
                cmd.Parameters["@Id"].Value = r.ObjectId;
                this.cnx.Open();
                cmd.ExecuteNonQuery();
                this.cnx.Close();

                cmd.CommandText = "DELETE FROM Relations WHERE RelationId = @Id";
                cmd.Parameters["@Id"].Value = r.ObjectId;
                this.cnx.Open();
                cmd.ExecuteNonQuery();
                this.cnx.Close();

                foreach (CardinalEntity ce in r.GetEntities())
                {
                    DeleteEntity(ce.Entity);
                }

                foreach (Atribute a in r.GetAtributes())
                {
                    DeleteAtribute(a);
                }
            }
            catch (Exception ex)
            {
                if (this.cnx.State != System.Data.ConnectionState.Closed)
                {
                    this.cnx.Close();
                }
                throw new Exception(ex.Message + "/r Error al borrar Relacion " + r.ObjectId);
            }
        }
        private void DeleteEntity(Entity e)
        {
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = this.cnx;
                cmd.Parameters.Add("@Id", System.Data.SqlDbType.Int);

                cmd.CommandText = "DELETE FROM EntityAtributes WHERE EntityId = @Id";
                cmd.Parameters["@Id"].Value = e.ObjectId;
                this.cnx.Open();
                cmd.ExecuteNonQuery();
                this.cnx.Close();

                foreach (Atribute a in e.GetAtributes())
                {
                    DeleteAtribute(a);
                }

                cmd.CommandText = "DELETE FROM Entity WHERE EntityId = @Id";
                cmd.Parameters["@Id"].Value = e.ObjectId;
                this.cnx.Open();
                cmd.ExecuteNonQuery();
                this.cnx.Close();
            }
            catch (Exception ex)
            {
                if (this.cnx.State != System.Data.ConnectionState.Closed)
                {
                    this.cnx.Close();
                }
                throw new Exception(ex.Message + "/r Error al borrar entidad "+e.ObjectId);
            }
        }
        private void DeleteAtribute(Atribute a)
        {
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = this.cnx;
                cmd.Parameters.Add("@Id", System.Data.SqlDbType.Int);

                cmd.CommandText = "DELETE FROM Atributes WHERE AtributeId = @Id";
                cmd.Parameters["@Id"].Value = a.ObjectId;
                this.cnx.Open();
                cmd.ExecuteNonQuery();
                this.cnx.Close();

                if (a.Type == Atribute.AtributeType.CompositeType)
                {
                    foreach (Atribute ca in a.GetAtributes())
                    {
                        DeleteAtribute(ca);
                    }
                }
            }
            catch (Exception ex)
            {
                if (this.cnx.State != System.Data.ConnectionState.Closed)
                {
                    this.cnx.Close();
                }
                throw new Exception(ex.Message + "/r Error al borrar Atributo "+a.ObjectId);
            }
        }

        private void PersistEntities(Mer mer)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = this.cnx;
            cmd.Parameters.Add("@Name", System.Data.SqlDbType.VarChar);
            cmd.Parameters.Add("@isWeak", System.Data.SqlDbType.Bit);
            cmd.Parameters.Add("@MerId", System.Data.SqlDbType.Int);
            cmd.Parameters["@MerId"].Value = this.merId;

            cmd.CommandText = "INSERT INTO Entity (MerId, Name, isWeak) Values(@MerId, @Name, @isWeak)";
            
            foreach (Entity e in mer.GetEntities())
            {
                cmd.Parameters["@Name"].Value = e.Name;
                cmd.Parameters["@isWeak"].Value = e.IsWeak;
                this.cnx.Open();
                cmd.ExecuteNonQuery();
                e.ObjectId = GetId("Entity", "EntityId");
                this.cnx.Close();
                PersistAtributes(e, e.ObjectId);
            }
        }
        private void PersistAtributes(IAtributeObject obj, int objectId)
        {
            SqlCommand cmdRelation = new SqlCommand();
            cmdRelation.Connection = this.cnx;
            if (obj is Entity)
            {
                cmdRelation.CommandText = "INSERT INTO EntityAtributes VALUES (@EntityId, @AtributeId, @isKey, @Qty)";
            }
            else if (obj is Relationship)
            {
                cmdRelation.CommandText = "INSERT INTO RelationAtributes VALUES (@EntityId, @AtributeId, @isKey, @Qty)";
            }
            else
            {
                cmdRelation.CommandText = "INSERT INTO CompositeAtribute VALUES (@EntityId, @AtributeId, @isKey, @Qty)";
            }
            cmdRelation.Parameters.Add("@EntityId", System.Data.SqlDbType.Int);
            cmdRelation.Parameters.Add("@AtributeId", System.Data.SqlDbType.Int);
            cmdRelation.Parameters.Add("@isKey", System.Data.SqlDbType.Bit);
            cmdRelation.Parameters.Add("@Qty", System.Data.SqlDbType.Int);

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = this.cnx;
            
            cmd.Parameters.Add("@Name", System.Data.SqlDbType.VarChar);
            cmd.Parameters.Add("@Type", System.Data.SqlDbType.VarChar);
            
            cmd.CommandText = "INSERT INTO Atributes (Name, Type) Values(@Name, @Type)";

            foreach (Atribute a in obj.GetAtributes())
            {
                cmd.Parameters["@Name"].Value = a.Name;
                cmd.Parameters["@Type"].Value = a.Type;

                cnx.Open();
                cmd.ExecuteNonQuery();
                a.ObjectId = GetId("Atributes", "AtributeId");
                cnx.Close();

                cmdRelation.Parameters["@EntityId"].Value = objectId;
                cmdRelation.Parameters["@AtributeId"].Value = a.ObjectId;
                cmdRelation.Parameters["@isKey"].Value = a.IsPrimaryKey;
                cmdRelation.Parameters["@Qty"].Value = a.Cantidad;

                cnx.Open();
                cmdRelation.ExecuteNonQuery();
                cnx.Close();
                if (a.Type == Atribute.AtributeType.CompositeType)
                {
                    PersistAtributes(a, a.ObjectId);
                }
            }
        }
        private void PersistRelations(Mer mer)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = this.cnx;
            cmd.Parameters.Add("@Name", System.Data.SqlDbType.VarChar);
            cmd.Parameters.Add("@MerId", System.Data.SqlDbType.Int);
            cmd.Parameters["@MerId"].Value = this.merId;

            cmd.CommandText = "INSERT INTO Relations (MerId, Name) Values(@MerId, @Name)";

            foreach (Relationship r in mer.GetRelations())
            {
                cmd.Parameters["@Name"].Value = r.Name;
                this.cnx.Open();
                cmd.ExecuteNonQuery();
                r.ObjectId = GetId("Relations", "RelationId");
                this.cnx.Close();
                PersistAtributes(r, r.ObjectId);
                foreach (CardinalEntity ce in r.GetEntities())
                {
                    PersistCardinalEntity(ce, r.ObjectId);
                }
            }
        }
        private void PersistCardinalEntity(CardinalEntity ce, int relationId)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = this.cnx;
            cmd.Parameters.Add("@RelId", System.Data.SqlDbType.Int);
            cmd.Parameters.Add("@EntityId", System.Data.SqlDbType.Int);
            cmd.Parameters.Add("@Cardinality", System.Data.SqlDbType.VarChar);

            cmd.Parameters["@RelId"].Value = relationId;
            cmd.Parameters["@EntityId"].Value = ce.Entity.ObjectId;
            cmd.Parameters["@Cardinality"].Value = ce.Cardinality;

            cmd.CommandText = "INSERT INTO RelationEntity (RelationId, EntityId, Cardinality) Values(@RelId, @EntityId, @Cardinality)";

            cnx.Open();
            cmd.ExecuteNonQuery();
            cnx.Close();
        }
        private int GetId(string tableName, string columnName)
        {
            string cmdText = "SELECT TOP 1 " + columnName + " FROM " + tableName + " ORDER BY "+ columnName + " DESC";
            SqlCommand cmd = new SqlCommand(cmdText, this.cnx);
            
            return Convert.ToInt32(cmd.ExecuteScalar());
        }
    }
}
